"""
Case Type   : fullsql
Case Name   : track_stmt_stat_level参数为off/l1,入参相同时,slow_query与full_sql相同字段值相等
Create At   : 2022/01/30
Owner       : zhao-pengcheng-g
Description :
    1.检查参数，修改配置:enable_copy_server_files=on
    2.检查参数，修改配置:instr_unique_sql_count=100000
    3.创建用户，创建表
    4.导入数据
    5.设置log_min_duration_statement为0
    6.设置set track_stmt_stat_level='off,l1';
    使用普通用户执行query
    7.查询dbe_perf.get_global_slow_sql_by_timestamp
    8.查询slow_sql minus full_sql为空
    9.查询full_sql minus slow_sql为空
    10.环境清理
Expect      :
    1.修改参数成功
    2.修改参数成功
    3.创建成功
    4.导入成功
    5.设置成功
    6.设置成功,执行query成功，1条数据
    7.存在数据
    8.没有记录
    9.没有记录
    10.环境清理成功
History     :
"""

import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Case0021 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.rootNode = Node('PrimaryRoot')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_name_1 = 't_lineitem_fullsql_0021_1'
        self.table_name_2 = 't_supplier_fullsql_0021_2'
        self.table_name_3 = 't_orders_fullsql_0021_3'
        self.table_name_4 = 't_nation_fullsql_0021_4'
        self.user_name = f'u_fullsql_0021'
        self.table_path = os.path.join(r'/home',
                                       self.userNode.ssh_user, 'copy_tables')

        text = '---prestep1:修改机器umask---'
        self.logger.info(text)
        umask_cmd = f"umask 0022;"
        self.rootNode.sh(umask_cmd).result()
        text = '---step1:检查参数，修改配置:enable_copy_server_files=on,' \
               'expect:成功---'
        self.logger.info(text)
        self.config_copy = 'enable_copy_server_files'
        self.show_copy_cmd = f'show {self.config_copy};'
        check_copy = self.sh_primy.execut_db_sql(self.show_copy_cmd)
        self.logger.info(check_copy)
        self.copy_default = check_copy.splitlines()[-2].strip()
        if 'on' != self.copy_default:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_copy}=on')
            self.assertTrue(result, '参数修改失败')
        chk_copy_msg = self.sh_primy.execut_db_sql(self.show_copy_cmd)
        self.logger.info(chk_copy_msg)
        self.common.equal_sql_mdg(chk_copy_msg, self.config_copy, 'on',
                                  '(1 row)', flag='1')

        text = '---step2:检查参数，修改配置:instr_unique_sql_count=100000,' \
               'expect:成功---'
        self.logger.info(text)
        self.config_instr = 'instr_unique_sql_count'
        self.show_instr_cmd = f'show {self.config_instr};'
        check_instr = self.sh_primy.execut_db_sql(self.show_instr_cmd)
        self.logger.info(check_instr)
        self.instr_default = check_instr.splitlines()[-2].strip()
        if '100000' != self.instr_default:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_instr}=100000')
            self.assertTrue(result, '参数修改失败')
        chk_instr_msg = self.sh_primy.execut_db_sql(self.show_instr_cmd)
        self.logger.info(chk_instr_msg)
        self.common.equal_sql_mdg(chk_instr_msg, self.config_instr, '100000',
                                  '(1 row)', flag='1')

        text = '---step3:清理历史记录,创建用户，创建表,expect;成功---'
        self.logger.info(text)
        sql_cmd = f'''select reset_unique_sql('global', 'all', 1);
                    drop user if exists {self.user_name} cascade;
                    create user {self.user_name} sysadmin \
                    password '{macro.COMMON_PASSWD}'; '''
        self.logger.info(sql_cmd)
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn('reset_unique_sql' and '(1 row)',
                      sql_msg, '执行失败:' + text)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG and
                      self.constant.CREATE_ROLE_SUCCESS_MSG,
                      sql_msg, '执行失败:' + text)
        text = '---step3.1:切换用户,创建lineitem表,expect:创建成功 ---'
        self.logger.info(text)
        create_lineitem = f'''set role {self.user_name} \
            password '{macro.COMMON_PASSWD}';
            set current_schema to public;
            drop table if exists {self.table_name_1};
            create table {self.table_name_1}
            (
            l_orderkey    bigint not null, 
            l_partkey     bigint not null, 
            l_suppkey     bigint not null, 
            l_linenumber  bigint not null, 
            l_quantity    decimal(15,2) not null, 
            l_extendedprice  decimal(15,2) not null, 
            l_discount    decimal(15,2) not null, 
            l_tax         decimal(15,2) not null, 
            l_returnflag  char(1) not null, 
            l_linestatus  char(1) not null, 
            l_shipdate    date not null, 
            l_commitdate  date not null, 
            l_receiptdate date not null, 
            l_shipinstruct char(25) not null, 
            l_shipmode     char(10) not null, 
            l_comment      varchar(44) not null
            )
            partition by range(l_shipdate)
            (
            partition l_shipdate_1 values less than('1993-01-01 00:00:00'),
            partition l_shipdate_2 values less than('1994-01-01 00:00:00'),
            partition l_shipdate_3 values less than('1995-01-01 00:00:00'),
            partition l_shipdate_4 values less than('1996-01-01 00:00:00'),
            partition l_shipdate_5 values less than('1997-01-01 00:00:00'),
            partition l_shipdate_6 values less than('1998-01-01 00:00:00'),
            partition l_shipdate_7 values less than('1999-01-01 00:00:00')
            ); '''
        msg_lineitem = self.sh_primy.execut_db_sql(create_lineitem)
        self.logger.info(msg_lineitem)
        self.assertTrue(msg_lineitem.count(self.constant.SET_SUCCESS_MSG) == 2,
                        '执行失败:' + text)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS, msg_lineitem,
                      '执行失败:' + text)
        text = '---step3.2:切换用户,创建supplier表,expect:创建成功 ---'
        self.logger.info(text)
        create_supplier = f'''set role {self.user_name} \
            password '{macro.COMMON_PASSWD}';
            set current_schema to public;
            drop table if exists {self.table_name_2};
            create table {self.table_name_2}
                (s_suppkey     bigint not null, 
                s_name        char(25) not null, 
                s_address     varchar(40) not null, 
                s_nationkey   int not null, 
                s_phone       char(15) not null, 
                s_acctbal     decimal(15,2) not null, 
                s_comment     varchar(101) not null
                )
                partition by range(s_nationkey)
                (
                partition s_nationkey_1 values less than(1),
                partition s_nationkey_2 values less than(2),
                partition s_nationkey_3 values less than(3),
                partition s_nationkey_4 values less than(4),
                partition s_nationkey_5 values less than(5),
                partition s_nationkey_6 values less than(6),    
                partition s_nationkey_7 values less than(7),
                partition s_nationkey_8 values less than(8),
                partition s_nationkey_9 values less than(9),
                partition s_nationkey_10 values less than(10),
                partition s_nationkey_11 values less than(11),
                partition s_nationkey_12 values less than(12),
                partition s_nationkey_13 values less than(13),
                partition s_nationkey_14 values less than(14),
                partition s_nationkey_15 values less than(15),
                partition s_nationkey_16 values less than(16),
                partition s_nationkey_17 values less than(17),
                partition s_nationkey_18 values less than(18),
                partition s_nationkey_19 values less than(19),
                partition s_nationkey_20 values less than(20),
                partition s_nationkey_21 values less than(21),
                partition s_nationkey_22 values less than(22),
                partition s_nationkey_23 values less than(23),
                partition s_nationkey_24 values less than(24),
                partition s_nationkey_25 values less than(25));'''
        msg_supplier = self.sh_primy.execut_db_sql(create_supplier)
        self.logger.info(msg_supplier)
        self.assertTrue(msg_supplier.count(self.constant.SET_SUCCESS_MSG) == 2,
                        '执行失败:' + text)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS, msg_supplier,
                      '执行失败:' + text)
        text = '---step3.3:切换用户,创建orders表,expect:创建成功 ---'
        self.logger.info(text)
        create_orders = f'''set role {self.user_name} \
            password '{macro.COMMON_PASSWD}';
            set current_schema to public;
            drop table if exists {self.table_name_3};
            create table {self.table_name_3}
                (
                o_orderkey       bigint not null, 
                o_custkey        bigint not null, 
                o_orderstatus    char(1) not null, 
                o_totalprice     decimal(15,2) not null, 
                o_orderdate      date not null, 
                o_orderpriority  char(15) not null, 
                o_clerk          char(15) not null, 
                o_shippriority   bigint not null, 
                o_comment        varchar(79) not null
                )
            partition by range(o_orderdate)
            (
            partition o_orderdate_1 values less than('1993-01-01 00:00:00'),
            partition o_orderdate_2 values less than('1994-01-01 00:00:00'),
            partition o_orderdate_3 values less than('1995-01-01 00:00:00'),
            partition o_orderdate_4 values less than('1996-01-01 00:00:00'),
            partition o_orderdate_5 values less than('1997-01-01 00:00:00'),
            partition o_orderdate_6 values less than('1998-01-01 00:00:00'),
            partition o_orderdate_7 values less than('1999-01-01 00:00:00')
            );'''
        msg_orders = self.sh_primy.execut_db_sql(create_orders)
        self.logger.info(msg_orders)
        self.assertTrue(msg_orders.count(self.constant.SET_SUCCESS_MSG) == 2,
                        '执行失败:' + text)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS, msg_orders,
                      '执行失败:' + text)
        text = '---step3.4:切换用户,创建nation表,,expect:创建成功 ---'
        self.logger.info(text)
        create_nation = f'''set role {self.user_name} \
            password '{macro.COMMON_PASSWD}';
            set current_schema to public;
            drop table if exists {self.table_name_4};
            create table {self.table_name_4}(n_nationkey int not null, 
            n_name char(25) not null, n_regionkey int not null, 
            n_comment varchar(152)); '''
        nation_msg = self.sh_primy.execut_db_sql(create_nation)
        self.logger.info(nation_msg)
        self.assertTrue(nation_msg.count(self.constant.SET_SUCCESS_MSG) == 2,
                        '执行失败:' + text)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS,
                      nation_msg, '执行失败:' + text)

        text = '---step4:导入数据，expect:导入成功---'
        self.logger.info(text)
        mkdir_cmd = f'rm -rf {self.table_path};' \
                    f'mkdir {self.table_path}'
        self.userNode.sh(mkdir_cmd)
        self.common.scp_file(self.userNode, 'lineitem.tbl',
                             self.table_path)
        self.common.scp_file(self.userNode, 'supplier.tbl',
                             self.table_path)
        self.common.scp_file(self.userNode, 'orders.tbl',
                             self.table_path)
        self.common.scp_file(self.userNode, 'nation.tbl',
                             self.table_path)
        lineitem_path = os.path.join(self.table_path, 'lineitem.tbl')
        supplier_path = os.path.join(self.table_path, 'supplier.tbl')
        orders_path = os.path.join(self.table_path, 'orders.tbl')
        nation_path = os.path.join(self.table_path, 'nation.tbl')
        copy_cmd = f'''set role {self.user_name} \
                    password '{macro.COMMON_PASSWD}';
                    copy {self.table_name_1} from '{lineitem_path}' \
                    using delimiters '|';
                    copy {self.table_name_2} from '{supplier_path}' \
                    using delimiters '|';
                    copy {self.table_name_3} from '{orders_path}' \
                    using delimiters '|';
                    copy {self.table_name_4} from '{nation_path}' \
                    using delimiters '|';
                    analyze;'''
        self.logger.info(copy_cmd)
        copy_msg = self.sh_primy.execut_db_sql(copy_cmd)
        self.logger.info(copy_msg)
        self.assertTrue(copy_msg.count(self.constant.copy_success_msg) == 4,
                        '执行失败:' + text)
        self.assertIn(self.constant.SET_SUCCESS_MSG
                      and self.constant.ANALYZE_SUCCESS_MSG,
                      copy_msg, '执行失败:' + text)

    def test_fullsql(self):
        text = '---step5:设置log_min_duration_statement为0,expect:设置成功 ---'
        self.logger.info(text)
        self.config_duratio = 'log_min_duration_statement'
        self.show_duration_cmd = f'show {self.config_duratio};'
        check_duration = self.sh_primy.execut_db_sql(self.show_duration_cmd)
        self.logger.info(check_duration)
        self.duration_default = check_duration.splitlines()[-2].strip()
        if '0' != self.duration_default:
            guc_duration = self.sh_primy.execute_gsguc('reload',
                                                       self.constant.GSGUC_SUCCESS_MSG,
                                                       f'{self.config_duratio}=0')
            self.assertTrue(guc_duration, '执行失败:' + text)
        check_duration_msg = self.sh_primy.execut_db_sql(self.show_duration_cmd)
        self.logger.info(check_duration_msg)
        self.common.equal_sql_mdg(check_duration_msg, self.config_duratio, '0',
                                  '(1 row)', flag='1')

        text = "---step6:设置track_stmt_stat_level='off,l1';" \
               "使用普通用户执行query,expect:设置成功，执行query成功---"
        self.logger.info(text)
        sql_cmd = f'''set role {self.user_name} \
            password '{macro.COMMON_PASSWD}';
            set track_stmt_stat_level ='off,l1';
            show track_stmt_stat_level;
            select 
                s_name, count(*) as numwait 
            from 
                {self.table_name_2},
                {self.table_name_1} tb1,
                {self.table_name_3},
                {self.table_name_4}
            where 
                s_suppkey = tb1.l_suppkey 
                and o_orderkey = tb1.l_orderkey 
                and o_orderstatus = 'F' 
                and tb1.l_receiptdate > tb1.l_commitdate 
                and exists (
                    select * from {self.table_name_1} tb2 
                    where tb2.l_orderkey = tb1.l_orderkey 
                        and tb2.l_suppkey <> tb1.l_suppkey) 
                and not exists (
                    select * from {self.table_name_1} tb3 
                    where tb3.l_orderkey = tb1.l_orderkey 
                        and tb3.l_suppkey <> tb1.l_suppkey 
                        and tb3.l_receiptdate > tb3.l_commitdate)
                and s_nationkey = n_nationkey 
                and n_name = 'SAUDI ARABIA' 
            group by s_name 
            order by numwait desc,s_name limit 100;
            select pg_sleep(100);'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertTrue(sql_msg.count(self.constant.SET_SUCCESS_MSG) == 2,
                        '执行失败:' + text)
        self.assertIn('off,l1' and '(100 rows)', sql_msg, '执行失败:' + text)

        text = '---step7:查询dbe_perf.get_global_slow_sql_by_timestamp,' \
               'expect:存在数据---'
        self.logger.info(text)
        sql_cmd = f'''select * from \
            dbe_perf.get_global_slow_sql_by_timestamp\
            (clock_timestamp() - 600,clock_timestamp()) \
            where unique_query_id=(select unique_sql_id from \
            dbe_perf.statement where query like '%numwait%'); '''
        exec_msg = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.logger.info(exec_msg)
        self.assertIn('(1 row)', exec_msg, '执行失败:' + text)

        text = '---step8:查询slow_sql minus full_sql为空,expect:没有记录---'
        self.logger.info(text)
        fullsql_cmd = f'''(select * 
            from dbe_perf.get_global_slow_sql_by_timestamp\
                (clock_timestamp() - 1/24,clock_timestamp()) 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement 
                where query like '%numwait%')) 
            minus all (select * 
                from dbe_perf.get_global_full_sql_by_timestamp\
                    (clock_timestamp() - 1/24,clock_timestamp()) 
                where unique_query_id=(select unique_sql_id 
                    from dbe_perf.statement 
                    where query like '%numwait%'));'''
        fullsql_msg = self.sh_primy.execut_db_sql(fullsql_cmd,
                                                  dbname='postgres')
        self.logger.info(fullsql_msg)
        self.assertIn('(0 rows)', fullsql_msg, '执行失败:' + text)

        text = '---step9:查询full_sql minus slow_sql为空,expect:没有记录---'
        self.logger.info(text)
        fullsql_cmd = f'''(select * 
            from dbe_perf.get_global_full_sql_by_timestamp\
                (clock_timestamp() - 1/24,clock_timestamp()) 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement 
                where query like '%numwait%')) 
            minus all(select * 
            from dbe_perf.get_global_slow_sql_by_timestamp\
                (clock_timestamp() - 1/24,clock_timestamp()) 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement 
                where query like '%numwait%'));'''
        fullsql_msg = self.sh_primy.execut_db_sql(fullsql_cmd,
                                                  dbname='postgres')
        self.logger.info(fullsql_msg)
        self.assertIn('(0 rows)', fullsql_msg, '执行失败:' + text)

    def tearDown(self):
        text_10 = '---step10:环境清理，expect:成功---'
        self.logger.info(text_10)
        delete_cmd = f'''delete from statement_history \
                where unique_query_id=(select unique_sql_id 
                    from dbe_perf.statement 
                    where query like '%numwait%');'''
        delete_msg = self.sh_primy.execut_db_sql(delete_cmd, dbname='postgres')
        self.logger.info(delete_msg)
        text = '---step10.1检查参数，恢复原值:enable_copy_server_files，' \
               'expect:成功---'
        self.logger.info(text)
        check_copy = self.sh_primy.execut_db_sql(self.show_copy_cmd)
        self.logger.info(check_copy)
        current = check_copy.splitlines()[-2].strip()
        if self.copy_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_copy}={self.copy_default}')
            self.assertTrue(result, '参数恢复失败')
        show_copy_msg = self.sh_primy.execut_db_sql(self.show_copy_cmd)
        self.logger.info(show_copy_msg)

        text = '---step10.2检查参数，恢复原值:instr_unique_sql_count，' \
               'expect:成功---'
        self.logger.info(text)
        check_instr = self.sh_primy.execut_db_sql(self.show_instr_cmd)
        self.logger.info(check_instr)
        current = check_instr.splitlines()[-2].strip()
        if self.instr_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_instr}={self.instr_default}')
            self.assertTrue(result, '参数恢复失败')
        show_instr_msg = self.sh_primy.execut_db_sql(self.show_instr_cmd)
        self.logger.info(show_instr_msg)

        text = '---step10.3检查参数，恢复原值:log_min_duration_statement，' \
               'expect:成功---'
        self.logger.info(text)
        check_duration = self.sh_primy.execut_db_sql(self.show_duration_cmd)
        self.logger.info(check_duration)
        current = check_duration.splitlines()[-2].strip()
        if self.duration_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_duratio}={self.duration_default}')
            self.assertTrue(result, '参数恢复失败')
        show_slot_msg = self.sh_primy.execut_db_sql(self.show_duration_cmd)
        self.logger.info(show_slot_msg)

        text = '---step10.4删除表，删除用户,删除文件夹，expect:成功---'
        self.logger.info(text)
        drop_cmd = f'''drop owned by {self.user_name} cascade;
                       drop user {self.user_name};'''
        drop_msg = self.sh_primy.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)
        rm_cmd = f'rm -rf {self.table_path};'
        self.rootNode.sh(rm_cmd).result()
        self.assertIn(self.constant.DROP_OWNED_SUCCESS and
                      self.constant.DROP_ROLE_SUCCESS_MSG,
                      drop_msg, '执行失败:' + text)
        self.assertFalse(os.path.exists(self.table_path), '执行失败:' + text)
        self.common.equal_sql_mdg(show_copy_msg, self.config_copy,
                                  self.copy_default, '(1 row)', flag='1')
        self.common.equal_sql_mdg(show_instr_msg, self.config_instr,
                                  self.instr_default, '(1 row)', flag='1')
        self.common.equal_sql_mdg(show_slot_msg, self.config_duratio,
                                  self.duration_default, '(1 row)', flag='1')
        self.assertIn(self.constant.DELETE_SUCCESS_MSG,
                      delete_msg, '执行失败:' + text_10)
        self.logger.info(
            '---Opengauss_Function_Fullsql_Case0021 finish---')
